﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace MOVIE_THEATER
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void tabPage1_Click(object sender, EventArgs e)
        {
            this.PhimcomboBox.DataSource = SqlDataAccessHelper.ExecuteQuery("select tenphim, maphim from phim");
            this.PhimcomboBox.DisplayMember = "tenPhim";
            this.PhimcomboBox.ValueMember = "maphim";

            //load gio chieu phim
            if (this.PhimcomboBox.SelectedValue == null)
                return;

            string maphimselected = this.PhimcomboBox.SelectedValue.ToString();

            this.ThoiGianChieucomboBox.DataSource = SqlDataAccessHelper.ExecuteQuery("select sc.thoigianbatdau , ct.mactsuatchieu from phim p join ctsuatchieu ct on p.maphim = ct.maphim join suatchieu sc on sc.masuatchieu = ct.masuatchieu where p.maphim = '" + maphimselected + "'");
            this.ThoiGianChieucomboBox.DisplayMember = "thoigianbatdau";
            this.ThoiGianChieucomboBox.ValueMember = "mactsuatchieu";

            //load danh sach ve chua mua
            string mactsuatchieuSelected = "";

            if (this.ThoiGianChieucomboBox.SelectedValue != null)
            {
                mactsuatchieuSelected = this.ThoiGianChieucomboBox.SelectedValue.ToString();
                this.danhSachVedataGridView1.DataSource = SqlDataAccessHelper.ExecuteQuery("select v.nguoilon_treem, v.mave , v.tinhtrangve,  dg.dayghe, dg.soghe, v.sdt, v.cmnd, v.hotenkh from ve v join dayghe_phongchieu dg on v.madaypc = dg.madaypc where v.tinhtrangve != 2 and v.MaCTSuatChieu = '" + mactsuatchieuSelected + "'");
                for (int i = 2; i < int.Parse(this.danhSachVedataGridView1.Columns.Count.ToString()); i++)
                    this.danhSachVedataGridView1.Columns[i].ReadOnly = true;
            }
            //
        }

        private void groupBox1_Enter(object sender, EventArgs e)
        {

        }

        private void label1_Click(object sender, EventArgs e)
        {

        }

        private void tabPage5_Click(object sender, EventArgs e)
        {

        }

        //CHECK HERE
        // set column name, and column width
        // set isolation level for transaction
        // set disable edit in datagridview
        private void reportBut_Click(object sender, EventArgs e)
        {
            try
            {
                int criteriaIndex = this.criteriaSBox.SelectedIndex;
                int dateTimeIndex = this.timeCriteriaSBox.SelectedIndex;

                string spName = "";
                string sumCom = "";
                int dateType = 1;
                string criteriaColName = "";
                string dateTimeColName = "Thời Gian";

                switch (criteriaIndex)
                {
                    case 0:
                        spName = "sp_DoanhThuTheoThoiGian";
                        criteriaColName = "Doanh Thu (VND)";
                        sumCom = "sp_TongDoanhThu";
                        break;
                    case 1:
                        spName = "sp_SoVeDaThanhToanTheoThoiGian";
                        criteriaColName = "Số Lượng Vé";
                        sumCom = "sp_TongSoVeDaThanhToan";
                        break;
                    case 2:
                        spName = "sp_SoVeDaDatTheoThoiGian";
                        criteriaColName = "Số Lượng Vé";
                        sumCom = "sp_TongSoVeDaDat";
                        break;
                    default:
                        MessageBox.Show("Tiêu chí thống kê không hợp lệ");
                        return;
                }
                switch (dateTimeIndex)
                {
                    case 0:
                        dateType = 1;
                        break;
                    case 1: dateType = 2;
                        break;
                    case 2: dateType = 3;
                        break;
                    default:
                        MessageBox.Show("Thông tin thời gian không hợp lệ");
                        return;
                }
                string query1 = " EXEC " + spName + " " + dateType.ToString();
                sumCom = " EXEC " + sumCom;
                //CODE SOLVE FOR SERIALIZABLE CONFLICT
                //string delayQuery = " WAITFOR DELAY '0:0:10' ";
                //string isolationLevel = "SET TRAN ISOLATION LEVEL SERIALIZABLE ";
                //string[] queryArr = {isolationLevel, query1, delayQuery, sumCom };
                //bool[] isNoneQuery = {true, false, true,false };

                string[] queryArr = { query1, sumCom };
                bool[] isNoneQuery = { false, false };

                List<DataTable> result = SqlDataAccessHelper.executeTransaction(queryArr, isNoneQuery);
                this.reportGridView.DataSource = result[0];
                this.reportGridView.Columns[0].HeaderText = dateTimeColName;
                this.reportGridView.Columns[0].Width = 300;
                this.reportGridView.Columns[1].HeaderText = criteriaColName;
                this.reportGridView.Columns[1].Width = 300;
                if (result[1].Rows.Count != 0)
                    this.totalLbl.Text = result[1].Rows[0]["Tong"].ToString();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void reportDelBut_Click(object sender, EventArgs e)
        {

            //CHECK HERE
            // set column name, and column width
            // set isolation level for transaction
            // set disable edit in datagridview

            try
            {
                int criteriaIndex = this.criteriaSBox.SelectedIndex;
                int dateTimeIndex = this.timeCriteriaSBox.SelectedIndex;

                string spName = "";
                string sumCom = "";
                int dateType = 1;
                string criteriaColName = "";
                string dateTimeColName = "Thời Gian";

                switch (criteriaIndex)
                {
                    case 0:
                        spName = "sp_DoanhThuTheoThoiGian";
                        criteriaColName = "Doanh Thu (VND)";
                        sumCom = "sp_TongDoanhThu";
                        break;
                    case 1:
                        spName = "sp_SoVeDaThanhToanTheoThoiGian";
                        criteriaColName = "Số Lượng Vé";
                        sumCom = "sp_TongSoVeDaThanhToan";
                        break;
                    case 2:
                        spName = "sp_SoVeDaDatTheoThoiGian";
                        criteriaColName = "Số Lượng Vé";
                        sumCom = "sp_TongSoVeDaDat";
                        break;
                    default:
                        MessageBox.Show("Tiêu chí thống kê không hợp lệ");
                        return;
                }
                switch (dateTimeIndex)
                {
                    case 0:
                        dateType = 1;
                        break;
                    case 1: dateType = 2;
                        break;
                    case 2: dateType = 3;
                        break;
                    default:
                        MessageBox.Show("Thông tin thời gian không hợp lệ");
                        return;
                }
                string query1 = " EXEC " + spName + " " + dateType.ToString();
                sumCom = " EXEC " + sumCom;
                //CODE SOLVE FOR SERIALIZABLE CONFLICT
                string delayQuery = " WAITFOR DELAY '0:0:10' ";
                //string isolationLevel = "SET TRAN ISOLATION LEVEL SERIALIZABLE ";
                //string[] queryArr = {isolationLevel, query1, delayQuery, sumCom };
                //bool[] isNoneQuery = {true, false, true,false };

                string[] queryArr = { query1, delayQuery, sumCom };
                bool[] isNoneQuery = { false, true, false };

                List<DataTable> result = SqlDataAccessHelper.executeTransaction(queryArr, isNoneQuery);
                this.reportGridView.DataSource = result[0];
                this.reportGridView.Columns[0].HeaderText = dateTimeColName;
                this.reportGridView.Columns[0].Width = 300;
                this.reportGridView.Columns[1].HeaderText = criteriaColName;
                this.reportGridView.Columns[1].Width = 300;
                if (result[1].Rows.Count != 0)
                    this.totalLbl.Text = result[1].Rows[0]["Tong"].ToString();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }


        private void reportDelFixBut_Click(object sender, EventArgs e)
        {
            try
            {
                int criteriaIndex = this.criteriaSBox.SelectedIndex;
                int dateTimeIndex = this.timeCriteriaSBox.SelectedIndex;

                string spName = "";
                string sumCom = "";
                int dateType = 1;
                string criteriaColName = "";
                string dateTimeColName = "Thời Gian";

                switch (criteriaIndex)
                {
                    case 0:
                        spName = "sp_DoanhThuTheoThoiGian";
                        criteriaColName = "Doanh Thu (VND)";
                        sumCom = "sp_TongDoanhThu";
                        break;
                    case 1:
                        spName = "sp_SoVeDaThanhToanTheoThoiGian";
                        criteriaColName = "Số Lượng Vé";
                        sumCom = "sp_TongSoVeDaThanhToan";
                        break;
                    case 2:
                        spName = "sp_SoVeDaDatTheoThoiGian";
                        criteriaColName = "Số Lượng Vé";
                        sumCom = "sp_TongSoVeDaDat";
                        break;
                    default:
                        MessageBox.Show("Tiêu chí thống kê không hợp lệ");
                        return;
                }
                switch (dateTimeIndex)
                {
                    case 0:
                        dateType = 1;
                        break;
                    case 1: dateType = 2;
                        break;
                    case 2: dateType = 3;
                        break;
                    default:
                        MessageBox.Show("Thông tin thời gian không hợp lệ");
                        return;
                }
                string query1 = " EXEC " + spName + " " + dateType.ToString();
                sumCom = " EXEC " + sumCom;
                //CODE SOLVE FOR SERIALIZABLE CONFLICT
                string delayQuery = " WAITFOR DELAY '0:0:10' ";
                string isolationLevel = "SET TRAN ISOLATION LEVEL SERIALIZABLE ";
                string[] queryArr = { isolationLevel, query1, delayQuery, sumCom };
                bool[] isNoneQuery = { true, false, true, false };

                //string[] queryArr = { query1, sumCom };
                //bool[] isNoneQuery = { false, false };

                List<DataTable> result = SqlDataAccessHelper.executeTransaction(queryArr, isNoneQuery);
                this.reportGridView.DataSource = result[0];
                this.reportGridView.Columns[0].HeaderText = dateTimeColName;
                this.reportGridView.Columns[0].Width = 300;
                this.reportGridView.Columns[1].HeaderText = criteriaColName;
                this.reportGridView.Columns[1].Width = 300;
                if (result[1].Rows.Count != 0)
                    this.totalLbl.Text = result[1].Rows[0]["Tong"].ToString();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void deleteBut_Click(object sender, EventArgs e)
        {
            try
            {
                DataGridViewSelectedRowCollection selRows = this.ticketGridView.SelectedRows;
                if (selRows.Count == 0)
                {
                    MessageBox.Show("Bạn chưa chọn dòng nào để hủy vé");
                    return;
                }

                DialogResult dres = MessageBox.Show("Bạn có chắc muốn hủy vé?", "", MessageBoxButtons.YesNo);
                if (dres == DialogResult.No)
                {
                    return;
                }

                foreach (DataGridViewRow dr in selRows)
                {
                    string maVe = dr.Cells[0].Value.ToString();
                    SqlParameter para = new SqlParameter("@maVe", SqlDbType.VarChar);
                    para.Value = maVe;
                    SqlParameter[] paraArr = { para };
                    SqlDataAccessHelper.executeStoreProcedure("sp_huyVe", paraArr);
                    this.ticketGridView.Rows.Remove(dr);
                }
                MessageBox.Show("Hủy vé thành công");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }            
        }

        private void button1_Click(object sender, EventArgs e)
        {
            try
            {
                DataGridViewSelectedRowCollection selRows = this.ticketGridView.SelectedRows;
                if (selRows.Count == 0)
                {
                    MessageBox.Show("Bạn chưa chọn vé để hủy !");
                    return;
                }

                foreach (DataGridViewRow dr in selRows)
                {
                    string maVe = dr.Cells[0].Value.ToString();
                    SqlParameter para = new SqlParameter("@maVe", SqlDbType.VarChar);
                    para.Value = maVe;
                    SqlParameter[] paraArr = { para };
                    SqlDataAccessHelper.executeStoreProcedure("sp_delete_ve_rollback", paraArr);
                    this.ticketGridView.Rows.Remove(dr);
                }
                MessageBox.Show("Hủy vé bị Rollback !");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }         
        }

        private void searchBut_Click(object sender, EventArgs e)
        {
            try
            {
                string name = this.nameTxtBox.Text.Trim();
                string phone = this.phoneTxtBox.Text.Trim();

                string query = " EXEC sp_TimKiemVe '" + name + "' , '" + phone + "' ";
                DataTable result = SqlDataAccessHelper.ExecuteQuery(query);
                if (result.Rows.Count == 0)
                {
                    this.ticketGridView.Rows.Clear();
                    MessageBox.Show("Không tìm thấy thông tin thỏa yêu cầu");
                    return;
                }
                this.ticketGridView.Rows.Clear();
                for (int i = 0; i < result.Rows.Count; i++)
                {
                    this.ticketGridView.Rows.Add(new object[] { result.Rows[i]["MaVe"].ToString(), result.Rows[i]["STTChoNgoi"].ToString(), result.Rows[i]["MaDayPC"].ToString(), result.Rows[i]["TinhTrangVe"].ToString(),result.Rows[i]["NguoiLon_TreEm"].ToString(), result.Rows[i]["MaCTSuatChieu"].ToString(), result.Rows[i]["HoTenKH"].ToString(), result.Rows[i]["SDT"].ToString(), result.Rows[i]["CMND"].ToString(), result.Rows[i]["ThoiGianDatVe"].ToString() });
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void nameTxtBox_Click(object sender, EventArgs e)
        {
            this.nameTxtBox.Clear();
        }

        private void phoneTxtBox_Click(object sender, EventArgs e)
        {
            this.phoneTxtBox.Clear();
        }
               
        // phần đặt vé
        public void loadDataLostUpdateError()
        {
            string sophong = cb_lostupdate_error_sophong.Text;
            string phim = cb_lostupdate_namefilm_error.Text;
            string gio = cb_lostupdate_time_error.Text;
            try
            {
                ConnectDatabase connect = new ConnectDatabase();
                connect.ConnectDB();
                SqlCommand comm = new SqlCommand();
                DataTable d = new DataTable();
                comm.Connection = connect.conn;
                comm.CommandText = "sp_search";

                comm.CommandType = CommandType.StoredProcedure;
                comm.Parameters.Add(new SqlParameter("@soPhong", SqlDbType.Int));
                comm.Parameters["@soPhong"].Direction = ParameterDirection.Input;
                comm.Parameters["@soPhong"].Value = sophong;

                comm.Parameters.Add(new SqlParameter("@tenPhim", SqlDbType.NVarChar, 1000));
                comm.Parameters["@tenPhim"].Direction = ParameterDirection.Input;
                comm.Parameters["@tenPhim"].Value = phim;

                comm.Parameters.Add(new SqlParameter("@gioChieu", SqlDbType.DateTime));
                comm.Parameters["@gioChieu"].Direction = ParameterDirection.Input;
                comm.Parameters["@gioChieu"].Value = gio;


                SqlDataAdapter adap = new SqlDataAdapter(comm);
                adap.Fill(d);
                dgw_lostupdate_error.DataSource = d;
                if (d.Rows.Count == 0)
                {
                    MessageBox.Show("Không tìm thấy!", "Thông Báo");
                }
                else
                {
                    dgw_lostupdate_error.DataSource = d;

                    int n = dgw_lostupdate_error.Rows.Count - 1;
                    for (int i = 0; i < n; i++)
                    {
                        dgw_lostupdate_error.Rows[i].Cells[0].Value = 0;
                        if ((dgw_lostupdate_error.Rows[i].Cells[4].Value.ToString() == "1") || (dgw_lostupdate_error.Rows[i].Cells[4].Value.ToString() == "2"))
                            dgw_lostupdate_error.Rows[i].Cells[0].ReadOnly = true;
                    }
                }
            }
            catch (System.Exception ex)
            {
                MessageBox.Show(ex.Message, "Thông báo");
            }

        }
        // load dữ liệu tìm kiếm lên 
        public void loadDataLostUpdateFix()
        {
            string sophong = cb_lostupdate_fix_sophong.Text;
            string phim = cb_lostupdate_namefilm_fix.Text;
            string gio = cb_lostupdate_time_fix.Text;
            try
            {
                ConnectDatabase connect = new ConnectDatabase();
                connect.ConnectDB();
                SqlCommand comm = new SqlCommand();
                comm.Connection = connect.conn;
                DataTable d = new DataTable();
                comm.CommandText = "sp_search";

                comm.CommandType = CommandType.StoredProcedure;
                comm.Parameters.Add(new SqlParameter("@soPhong", SqlDbType.Int));
                comm.Parameters["@soPhong"].Direction = ParameterDirection.Input;
                comm.Parameters["@soPhong"].Value = sophong;

                comm.Parameters.Add(new SqlParameter("@tenPhim", SqlDbType.NVarChar, 1000));
                comm.Parameters["@tenPhim"].Direction = ParameterDirection.Input;
                comm.Parameters["@tenPhim"].Value = phim;

                comm.Parameters.Add(new SqlParameter("@gioChieu", SqlDbType.DateTime));
                comm.Parameters["@gioChieu"].Direction = ParameterDirection.Input;
                comm.Parameters["@gioChieu"].Value = gio;
                SqlDataAdapter adap = new SqlDataAdapter(comm);
                adap.Fill(d);
                dgw_lostudate_fix.DataSource = d;
                if (d.Rows.Count == 0)
                {
                    MessageBox.Show("Không tìm thấy!", "Thông Báo");
                }
                else
                {
                    dgw_lostudate_fix.DataSource = d;
                    int n = dgw_lostudate_fix.Rows.Count - 1;
                    for (int i = 0; i < n; i++)
                    {

                        dgw_lostudate_fix.Rows[i].Cells[0].Value = 0;
                        if ((dgw_lostudate_fix.Rows[i].Cells[4].Value.ToString() == "1") || (dgw_lostudate_fix.Rows[i].Cells[4].Value.ToString() == "2"))
                            dgw_lostudate_fix.Rows[i].Cells[0].ReadOnly = true;
                    }
                }

            }
            catch (System.Exception ex)
            {
                MessageBox.Show(ex.Message, "Thông báo");
            }
        }
        // load dữ liệu tìm kiếm lên 
        public void loadDataLostUpdateDeadlockError()
        {
            string sophong = cb_lostupdate_deadlock_error_sophong.Text;
            string phim = cb_lostupdate_deadlock_namefilm_error.Text;
            string gio = cb_lostupdate_deadlock_time_error.Text;
            try
            {
                ConnectDatabase connect = new ConnectDatabase();
                connect.ConnectDB();
                SqlCommand comm = new SqlCommand();
                comm.Connection = connect.conn;
                DataTable d = new DataTable();
                comm.CommandText = "sp_search";

                comm.CommandType = CommandType.StoredProcedure;
                comm.Parameters.Add(new SqlParameter("@soPhong", SqlDbType.Int));
                comm.Parameters["@soPhong"].Direction = ParameterDirection.Input;
                comm.Parameters["@soPhong"].Value = sophong;

                comm.Parameters.Add(new SqlParameter("@tenPhim", SqlDbType.NVarChar, 1000));
                comm.Parameters["@tenPhim"].Direction = ParameterDirection.Input;
                comm.Parameters["@tenPhim"].Value = phim;

                comm.Parameters.Add(new SqlParameter("@gioChieu", SqlDbType.DateTime));
                comm.Parameters["@gioChieu"].Direction = ParameterDirection.Input;
                comm.Parameters["@gioChieu"].Value = gio;
                SqlDataAdapter adap = new SqlDataAdapter(comm);
                adap.Fill(d);
                dgw_lostupdate_deadlock_error.DataSource = d;
                if (d.Rows.Count == 0)
                {
                    MessageBox.Show("Không tìm thấy!", "Thông Báo");
                }
                else
                {
                    dgw_lostupdate_deadlock_error.DataSource = d;
                    int n = dgw_lostupdate_deadlock_error.Rows.Count - 1;
                    for (int i = 0; i < n; i++)
                    {

                        dgw_lostupdate_deadlock_error.Rows[i].Cells[0].Value = 0;
                        if ((dgw_lostupdate_deadlock_error.Rows[i].Cells[4].Value.ToString() == "1") || (dgw_lostupdate_deadlock_error.Rows[i].Cells[4].Value.ToString() == "2"))
                            dgw_lostupdate_deadlock_error.Rows[i].Cells[0].ReadOnly = true;
                    }
                }

            }
            catch (System.Exception ex)
            {
                MessageBox.Show(ex.Message, "Thông báo");
            }
        }

        private void btn_search_error_Click(object sender, EventArgs e)
        {
            loadDataLostUpdateError();

        }

        private void btn_update_error_Click(object sender, EventArgs e)
        {
            try
            {
                int n = dgw_lostupdate_error.Rows.Count - 1;
                ConnectDatabase connect = new ConnectDatabase();
                connect.ConnectDB();
                SqlCommand comm = new SqlCommand();

                comm.Connection = connect.conn;
                for (int i = 0; i < n; i++)
                {


                    if (dgw_lostupdate_error.Rows[i].Cells[0].Value.Equals("1"))
                    {

                        string maVe = dgw_lostupdate_error.Rows[i].Cells[2].Value.ToString();
                        string TinhTrang = dgw_lostupdate_error.Rows[i].Cells[4].Value.ToString();
                        string NguoiLonhayTreEm = dgw_lostupdate_error.Rows[i].Cells[5].Value.ToString();
                        string sHoTenKH = dgw_lostupdate_error.Rows[i].Cells[8].Value.ToString();
                        string sSDT = dgw_lostupdate_error.Rows[i].Cells[9].Value.ToString();
                        string sCMND = dgw_lostupdate_error.Rows[i].Cells[10].Value.ToString();
                        DateTime sNgayDat = DateTime.Now;
                        if ((sSDT == "") || (sHoTenKH == "") || (sCMND == ""))
                            MessageBox.Show("Họ Tên Khách Hàng,Số Điện Thoại, Số CMND   không được để trống", "Thông Báo");
                        else
                        {

                            comm.CommandText = "sp_LostUpdate_error";
                            comm.CommandType = CommandType.StoredProcedure;
                            comm.Parameters.Add(new SqlParameter("@MaVe", SqlDbType.VarChar, 10));
                            comm.Parameters["@MaVe"].Direction = ParameterDirection.Input;
                            comm.Parameters["@MaVe"].Value = maVe;

                            comm.Parameters.Add(new SqlParameter("@LoaiVe", SqlDbType.Bit));
                            comm.Parameters["@LoaiVe"].Direction = ParameterDirection.Input;
                            comm.Parameters["@LoaiVe"].Value = NguoiLonhayTreEm;

                            comm.Parameters.Add(new SqlParameter("@HoTenKH", SqlDbType.VarChar, 50));
                            comm.Parameters["@HoTenKH"].Direction = ParameterDirection.Input;
                            comm.Parameters["@HoTenKH"].Value = sHoTenKH;

                            comm.Parameters.Add(new SqlParameter("@SDT", SqlDbType.NChar, 11));
                            comm.Parameters["@SDT"].Direction = ParameterDirection.Input;
                            comm.Parameters["@SDT"].Value = sSDT;

                            comm.Parameters.Add(new SqlParameter("@CMND", SqlDbType.NChar, 9));
                            comm.Parameters["@CMND"].Direction = ParameterDirection.Input;
                            comm.Parameters["@CMND"].Value = sCMND;

                            comm.Parameters.Add(new SqlParameter("@NgayDat", SqlDbType.DateTime));
                            comm.Parameters["@NgayDat"].Direction = ParameterDirection.Input;
                            comm.Parameters["@NgayDat"].Value = sNgayDat;

                            comm.Parameters.Add(new SqlParameter("@output", SqlDbType.Int));
                            comm.Parameters["@output"].Direction = ParameterDirection.Output;
                            comm.Parameters["@output"].Value = 0;
                            comm.ExecuteNonQuery();
                            int result = Int32.Parse(comm.Parameters["@output"].Value.ToString());
                            if (result == 1)
                            {
                                MessageBox.Show("Đặt vé thành công!", "Thông Báo");
                            }
                            else
                            {
                                MessageBox.Show("Đặt vé không thành công. Vé đã có người đặt", "Thông Báo");
                            }
                            loadDataLostUpdateError();
                        }

                    }
                }
                comm.Dispose();

            }
            catch (System.Exception ex)
            {
                MessageBox.Show(ex.Message, "Thông Báo");
            }
        }

        private void btn_cancle_error_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        private void btn_search_fix_Click(object sender, EventArgs e)
        {
            loadDataLostUpdateFix();
        }

        private void btn_update_fix_Click(object sender, EventArgs e)
        {
            try
            {
                int n = dgw_lostudate_fix.Rows.Count - 1;
                ConnectDatabase connect = new ConnectDatabase();
                connect.ConnectDB();
                SqlCommand comm = new SqlCommand();

                comm.Connection = connect.conn;
                for (int i = 0; i < n; i++)
                {


                    if (dgw_lostudate_fix.Rows[i].Cells[0].Value.Equals("1"))
                    {

                        string maVe = dgw_lostudate_fix.Rows[i].Cells[2].Value.ToString();
                        string TinhTrang = dgw_lostudate_fix.Rows[i].Cells[4].Value.ToString();
                        string MaHD = dgw_lostudate_fix.Rows[i].Cells[6].Value.ToString();
                        string NguoiLonhayTreEm = dgw_lostudate_fix.Rows[i].Cells[5].Value.ToString();
                        string sHoTenKH = dgw_lostudate_fix.Rows[i].Cells[8].Value.ToString();
                        string sSDT = dgw_lostudate_fix.Rows[i].Cells[9].Value.ToString();
                        string sCMND = dgw_lostudate_fix.Rows[i].Cells[10].Value.ToString();
                        DateTime sNgayDat = DateTime.Now;
                        if ((sSDT == "") || (sHoTenKH == "") || (sCMND == ""))
                            MessageBox.Show("Tên Khách Hàng,Số Điện Thoại, Số CMND   không được để trống", "Thông Báo");
                        else
                        {

                            comm.CommandText = "sp_LostUpdate_fix";
                            comm.CommandType = CommandType.StoredProcedure;
                            comm.Parameters.Add(new SqlParameter("@MaVe", SqlDbType.VarChar, 10));
                            comm.Parameters["@MaVe"].Direction = ParameterDirection.Input;
                            comm.Parameters["@MaVe"].Value = maVe;

                            comm.Parameters.Add(new SqlParameter("@LoaiVe", SqlDbType.Bit));
                            comm.Parameters["@LoaiVe"].Direction = ParameterDirection.Input;
                            comm.Parameters["@LoaiVe"].Value = NguoiLonhayTreEm;

                            comm.Parameters.Add(new SqlParameter("@HoTenKH", SqlDbType.VarChar, 50));
                            comm.Parameters["@HoTenKH"].Direction = ParameterDirection.Input;
                            comm.Parameters["@HoTenKH"].Value = sHoTenKH;

                            comm.Parameters.Add(new SqlParameter("@SDT", SqlDbType.NChar, 11));
                            comm.Parameters["@SDT"].Direction = ParameterDirection.Input;
                            comm.Parameters["@SDT"].Value = sSDT;

                            comm.Parameters.Add(new SqlParameter("@CMND", SqlDbType.NChar, 9));
                            comm.Parameters["@CMND"].Direction = ParameterDirection.Input;
                            comm.Parameters["@CMND"].Value = sCMND;

                            comm.Parameters.Add(new SqlParameter("@NgayDat", SqlDbType.DateTime));
                            comm.Parameters["@NgayDat"].Direction = ParameterDirection.Input;
                            comm.Parameters["@NgayDat"].Value = sNgayDat;


                            comm.Parameters.Add(new SqlParameter("@output", SqlDbType.Int));
                            comm.Parameters["@output"].Direction = ParameterDirection.Output;
                            comm.Parameters["@output"].Value = 0;
                            comm.ExecuteNonQuery();
                            int result = Int32.Parse(comm.Parameters["@output"].Value.ToString());
                            if (result == 1)
                            {
                                MessageBox.Show("Đặt vé thành công!", "Thông Báo");
                            }
                            else
                            {
                                MessageBox.Show("Đặt vé không thành công. Vé đã có người đặt", "Thông Báo");
                            }
                            loadDataLostUpdateFix();
                        }
                    }
                }
                comm.Dispose();

            }
            catch (System.Exception ex)
            {
                MessageBox.Show(ex.Message, "Thông Báo");
            }
        }

        private void btn_cancle_fix_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        private void btn_search_deadlock_error_Click(object sender, EventArgs e)
        {
            loadDataLostUpdateDeadlockError();
        }

        private void btn_update_deadlock_error_Click(object sender, EventArgs e)
        {
            try
            {
                int n = dgw_lostupdate_deadlock_error.Rows.Count - 1;
                ConnectDatabase connect = new ConnectDatabase();
                connect.ConnectDB();
                SqlCommand comm = new SqlCommand();

                comm.Connection = connect.conn;
                for (int i = 0; i < n; i++)
                {


                    if (dgw_lostupdate_deadlock_error.Rows[i].Cells[0].Value.Equals("1"))
                    {

                        string maVe = dgw_lostupdate_deadlock_error.Rows[i].Cells[2].Value.ToString();
                        string TinhTrang = dgw_lostupdate_deadlock_error.Rows[i].Cells[4].Value.ToString();
                        string MaHD = dgw_lostupdate_deadlock_error.Rows[i].Cells[6].Value.ToString();
                        string NguoiLonhayTreEm = dgw_lostupdate_deadlock_error.Rows[i].Cells[5].Value.ToString();
                        string sHoTenKH = dgw_lostupdate_deadlock_error.Rows[i].Cells[8].Value.ToString();
                        string sSDT = dgw_lostupdate_deadlock_error.Rows[i].Cells[9].Value.ToString();
                        string sCMND = dgw_lostupdate_deadlock_error.Rows[i].Cells[10].Value.ToString();
                        DateTime sNgayDat = DateTime.Now;
                        if ((sSDT == "") || (sHoTenKH == "") || (sCMND == ""))
                            MessageBox.Show("Họ Tên Khách Hàng,Số Điện Thoại, Số CMND   không được để trống", "Thông Báo");
                        else
                        {

                            comm.CommandText = "sp_LostUpdateDeadlock_erorr";
                            comm.CommandType = CommandType.StoredProcedure;
                            comm.Parameters.Add(new SqlParameter("@MaVe", SqlDbType.VarChar, 10));
                            comm.Parameters["@MaVe"].Direction = ParameterDirection.Input;
                            comm.Parameters["@MaVe"].Value = maVe;

                            comm.Parameters.Add(new SqlParameter("@LoaiVe", SqlDbType.Bit));
                            comm.Parameters["@LoaiVe"].Direction = ParameterDirection.Input;
                            comm.Parameters["@LoaiVe"].Value = NguoiLonhayTreEm;

                            comm.Parameters.Add(new SqlParameter("@HoTenKH", SqlDbType.VarChar, 50));
                            comm.Parameters["@HoTenKH"].Direction = ParameterDirection.Input;
                            comm.Parameters["@HoTenKH"].Value = sHoTenKH;

                            comm.Parameters.Add(new SqlParameter("@SDT", SqlDbType.NChar, 11));
                            comm.Parameters["@SDT"].Direction = ParameterDirection.Input;
                            comm.Parameters["@SDT"].Value = sSDT;

                            comm.Parameters.Add(new SqlParameter("@CMND", SqlDbType.NChar, 9));
                            comm.Parameters["@CMND"].Direction = ParameterDirection.Input;
                            comm.Parameters["@CMND"].Value = sCMND;

                            comm.Parameters.Add(new SqlParameter("@NgayDat", SqlDbType.DateTime));
                            comm.Parameters["@NgayDat"].Direction = ParameterDirection.Input;
                            comm.Parameters["@NgayDat"].Value = sNgayDat;


                            comm.Parameters.Add(new SqlParameter("@output", SqlDbType.Int));
                            comm.Parameters["@output"].Direction = ParameterDirection.Output;
                            comm.Parameters["@output"].Value = 0;
                            comm.ExecuteNonQuery();
                            int result = Int32.Parse(comm.Parameters["@output"].Value.ToString());
                            if (result == 1)
                            {
                                MessageBox.Show("Đặt vé thành công!", "Thông Báo");
                            }
                            else
                            {
                                MessageBox.Show("Đặt vé không thành công. Vé đã có người đặt", "Thông Báo");
                            }
                            loadDataLostUpdateDeadlockError();
                        }
                    }
                }
                comm.Dispose();

            }
            catch (System.Exception ex)
            {
                MessageBox.Show(ex.Message, "Thông Báo");
            }

        }

        private void btn_cancle_deadlock_error_Click(object sender, EventArgs e)
        {
            this.Close();
        }
        // kết thúc phần đặt vé
        // load dữ liệu vào combobox tìm kiếm
        private void Form1_Load(object sender, EventArgs e)
        {


            //load du lieu vao comboxbox danh sach ma chi tiet phieu suat
            this.MaCTPScomboBox1.DataSource = SqlDataAccessHelper.ExecuteQuery("select mactsuatchieu from ctsuatchieu");
            this.MaCTPScomboBox1.DisplayMember = "mactsuatchieu";
            this.MaCTPScomboBox1.ValueMember = "mactsuatchieu";

            if (this.MaCTPScomboBox1.SelectedValue != null)
            {
                string mactsc = this.MaCTPScomboBox1.SelectedValue.ToString();
                DataTable dt = SqlDataAccessHelper.ExecuteQuery("select giavetreem, giavenguoilon from ctsuatchieu where mactsuatchieu = '" + mactsc + "'");

                DataRow row = dt.Rows[0];
                int giagocnho = int.Parse(row[0].ToString());
                int giagoclon = int.Parse(row[1].ToString());

                if (this.gianguoilontextBox1.Text.ToString().Trim() == "")
                {
                    this.gianguoilontextBox1.Text = giagoclon.ToString();

                }

                if (this.giatreemtextBox2.Text.ToString().Trim() == "")
                {
                    this.giatreemtextBox2.Text = giagocnho.ToString();

                }
            }

            //LOAD DU LIEU VAO COMBOBOX TIM KIEM
            try
            {
                ConnectDatabase connect = new ConnectDatabase();
                connect.ConnectDB();
                SqlCommand comm = new SqlCommand();
                comm.Connection = connect.conn;
                DataTable d = new DataTable();
                comm.CommandText = "select TenPhim from PHIM ";
                SqlDataAdapter adap = new SqlDataAdapter(comm);
                adap.Fill(d);
                cb_lostupdate_namefilm_error.DataSource = d;
                cb_lostupdate_namefilm_error.DisplayMember = "TenPhim";
                cb_lostupdate_namefilm_fix.DataSource = d;
                cb_lostupdate_namefilm_fix.DisplayMember = "TenPhim";
                cb_lostupdate_deadlock_namefilm_error.DataSource = d;
                cb_lostupdate_deadlock_namefilm_error.DisplayMember = "TenPhim";


            }

            catch (System.Exception ex)
            {
                MessageBox.Show(ex.Message, "Thông báo");
            }
            try
            {
                ConnectDatabase connect = new ConnectDatabase();
                connect.ConnectDB();
                SqlCommand comm = new SqlCommand();
                comm.Connection = connect.conn;
                DataTable d1 = new DataTable();
                comm.CommandText = "select distinct ThoiGianBatDau from SUATCHIEU ";
                SqlDataAdapter adap1 = new SqlDataAdapter(comm);
                adap1.Fill(d1);

                cb_lostupdate_time_error.DataSource = d1;
                cb_lostupdate_time_error.DisplayMember = "ThoiGianBatDau";
                cb_lostupdate_time_fix.DataSource = d1;
                cb_lostupdate_time_fix.DisplayMember = "ThoiGianBatDau";
                cb_lostupdate_deadlock_time_error.DataSource = d1;
                cb_lostupdate_deadlock_time_error.DisplayMember = "ThoiGianBatDau";
            }
            catch (System.Exception ex)
            {
                MessageBox.Show(ex.Message, "Thông báo");
            }

                    }
        private void btmTimKiemConfict_Click(object sender, EventArgs e)
        {
            string tenphim = this.cbxTenPhim.Text.Trim();
            string soghe = this.tbxSoGhe.Text.Trim();

            ConnectDatabase Connect_DB = new ConnectDatabase();
            Connect_DB.ConnectDB();

            SqlCommand cmd = new SqlCommand();
            cmd.Connection = Connect_DB.conn;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "sp_TimVeTrong_Confict";

            SqlParameter p1 = new SqlParameter("@tenphim", tenphim);
            cmd.Parameters.Add(p1);
            SqlParameter p2 = new SqlParameter("@soghe", soghe);
            cmd.Parameters.Add(p2);
            try
            {
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataTable tb = new DataTable();

                da.Fill(tb);
                dgvDanhSachVeTrong.DataSource = tb;
                Connect_DB.conn.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
                Connect_DB.conn.Close();
            } 
        }

        private void btmTimKiemUnconflict_Click(object sender, EventArgs e)
        {
            string tenphim = this.cbxTenPhim.Text.Trim();
            string soghe = this.tbxSoGhe.Text.Trim();

            ConnectDatabase Connect_DB = new ConnectDatabase();
            Connect_DB.ConnectDB();

            SqlCommand cmd = new SqlCommand();
            cmd.Connection = Connect_DB.conn;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "sp_TimVeTrong_Unconfict";

            SqlParameter p1 = new SqlParameter("@tenphim", tenphim);
            cmd.Parameters.Add(p1);
            SqlParameter p2 = new SqlParameter("@soghe", soghe);
            cmd.Parameters.Add(p2);
            try
            {
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataTable tb = new DataTable();

                da.Fill(tb);
                dgvDanhSachVeTrong.DataSource = tb;
                Connect_DB.conn.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
                Connect_DB.conn.Close();
            }    
        }

       
        private void tabPage3_Click(object sender, EventArgs e)
        {

        }

        private void Suabutton3_Click(object sender, EventArgs e)
        {
            if (this.gianguoilontextBox1.Text.ToString().Trim() == "" && this.giatreemtextBox2.Text.ToString().Trim() == "")
            {
                MessageBox.Show("BẠN CHƯA NHẬP GIÁ VÉ CHỈNH SỬA");
                return;
            }

            string mactsc = this.MaCTPScomboBox1.SelectedValue.ToString();
            DataTable dt = SqlDataAccessHelper.ExecuteQuery("select giavetreem, giavenguoilon from ctsuatchieu where mactsuatchieu = '" + mactsc + "'");

            DataRow row = dt.Rows[0];
            int giagocnho = int.Parse(row[0].ToString());
            int giagoclon = int.Parse(row[1].ToString());

            if (this.gianguoilontextBox1.Text.ToString().Trim() == "")
            {
                this.gianguoilontextBox1.Text = giagoclon.ToString();

            }

            if (this.giatreemtextBox2.Text.ToString().Trim() == "")
            {
                this.giatreemtextBox2.Text = giagocnho.ToString();

            }


            SqlConnection connect = new SqlConnection("Data Source=localhost ;Initial Catalog=QLRAPPHIM;Integrated Security=True");

            try
            {
                connect.Open();

                SqlCommand comm = new SqlCommand();

                comm.Connection = connect;

                // @maCTSuatChieu varchar(10),
                //@GiaNguoiLon int,
                //@giaTreEm int


                int giaNguoiLon = int.Parse(this.gianguoilontextBox1.Text.ToString());
                int giaTreEm = int.Parse(this.giatreemtextBox2.Text.ToString());
                string maCTSC = this.MaCTPScomboBox1.SelectedValue.ToString();

                comm.CommandText = "sp_capNhatGia";
                comm.CommandType = CommandType.StoredProcedure;

                comm.Parameters.Add(new SqlParameter("@maCTSuatChieu", SqlDbType.VarChar, 10));
                comm.Parameters["@maCTSuatChieu"].Direction = ParameterDirection.Input;
                comm.Parameters["@maCTSuatChieu"].Value = maCTSC;

                comm.Parameters.Add(new SqlParameter("@GiaNguoiLon", SqlDbType.Int));
                comm.Parameters["@GiaNguoiLon"].Direction = ParameterDirection.Input;
                comm.Parameters["@GiaNguoiLon"].Value = giaNguoiLon;

                comm.Parameters.Add(new SqlParameter("@giaTreEm", SqlDbType.Int));
                comm.Parameters["@giaTreEm"].Direction = ParameterDirection.Input;
                comm.Parameters["@giaTreEm"].Value = giaTreEm;

                comm.ExecuteNonQuery();


                comm.Dispose();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }


            //LOAD DỮ LIỆU SAU KHI THAY ĐÔI
            string mactsc1 = this.MaCTPScomboBox1.SelectedValue.ToString();
            DataTable dt1 = SqlDataAccessHelper.ExecuteQuery("select giavetreem, giavenguoilon from ctsuatchieu where mactsuatchieu = '" + mactsc1 + "'");

            DataRow row1 = dt1.Rows[0];
            int giagocnho1 = int.Parse(row1[0].ToString());
            int giagoclon1 = int.Parse(row1[1].ToString());

            this.gianguoilontextBox1.Text = giagoclon1.ToString();
            this.giatreemtextBox2.Text = giagocnho1.ToString();

            MessageBox.Show("CẬP NHẬT VÉ VÀ ĐÃ ROLLBACK LAI");
        }

        private void MaCTPScomboBox1_SelectedIndexChanged(object sender, EventArgs e)
        {
            string mactsc = this.MaCTPScomboBox1.SelectedValue.ToString();
            DataTable dt = SqlDataAccessHelper.ExecuteQuery("select giavetreem, giavenguoilon from ctsuatchieu where mactsuatchieu = '" + mactsc + "'");
           
            if (dt.Rows.Count == 0)
                return;

            DataRow row = dt.Rows[0];
            int giagocnho = int.Parse(row[0].ToString());
            int giagoclon = int.Parse(row[1].ToString());

           
                this.gianguoilontextBox1.Text = giagoclon.ToString();
         

                this.giatreemtextBox2.Text = giagocnho.ToString();

        }

        private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
        {

        }

        private void cmndlabel_Click(object sender, EventArgs e)
        {

        }
        

        private void PhimcomboBox_SelectedIndexChanged(object sender, EventArgs e)
        {
            // load gio chieu phim
            string maphimselected = this.PhimcomboBox.SelectedValue.ToString();

            this.ThoiGianChieucomboBox.DataSource = SqlDataAccessHelper.ExecuteQuery("select sc.thoigianbatdau , ct.mactsuatchieu from phim p join ctsuatchieu ct on p.maphim = ct.maphim join suatchieu sc on sc.masuatchieu = ct.masuatchieu where p.maphim = '" + maphimselected + "'");
            this.ThoiGianChieucomboBox.DisplayMember = "thoigianbatdau";
            this.ThoiGianChieucomboBox.ValueMember = "mactsuatchieu";
            //load danh sach ve chua mua
            string mactsuatchieuSelected = "";

            if (this.ThoiGianChieucomboBox.SelectedValue != null)
            {
                mactsuatchieuSelected = this.ThoiGianChieucomboBox.SelectedValue.ToString();
                this.danhSachVedataGridView1.DataSource = SqlDataAccessHelper.ExecuteQuery("select v.nguoilon_treem, v.mave , v.tinhtrangve, dg.dayghe, dg.soghe, v.sdt, v.cmnd, v.hotenkh from ve v join dayghe_phongchieu dg on v.madaypc = dg.madaypc where v.tinhtrangve != 2 and v.MaCTSuatChieu = '" + mactsuatchieuSelected + "'");
                for (int i = 2; i < int.Parse(this.danhSachVedataGridView1.Columns.Count.ToString()); i++)
                    this.danhSachVedataGridView1.Columns[i].ReadOnly = true;
            }

        }

        private void ThoiGianChieucomboBox_SelectedIndexChanged(object sender, EventArgs e)
        {
            string mactsuatchieuSelected = "";
            try
            {

                if (this.ThoiGianChieucomboBox.SelectedValue != null)
                {
                    mactsuatchieuSelected = this.ThoiGianChieucomboBox.SelectedValue.ToString();
                    this.danhSachVedataGridView1.DataSource = SqlDataAccessHelper.ExecuteQuery("select v.nguoilon_treem, v.mave , v.tinhtrangve, dg.dayghe, dg.soghe, v.sdt, v.cmnd, v.hotenkh from ve v join dayghe_phongchieu dg on v.madaypc = dg.madaypc where v.tinhtrangve != 2 and v.MaCTSuatChieu = '" + mactsuatchieuSelected + "'");
                    for (int i = 2; i < int.Parse(this.danhSachVedataGridView1.Columns.Count.ToString()); i++)
                        this.danhSachVedataGridView1.Columns[i].ReadOnly = true;
                }
            }
            catch (Exception ex)
            {

            }
        }

        private void MuaErrorbutton1_Click(object sender, EventArgs e)
        {
            int tongtien = 0;

            int rows = int.Parse(this.danhSachVedataGridView1.RowCount.ToString());
            for (int i = 0; i < rows; i++)
            {

                if (this.danhSachVedataGridView1.Rows[i].Cells[0].Value != null)
                {

                    SqlConnection connect = new SqlConnection("Data Source=localhost ;Initial Catalog=QLRAPPHIM;Integrated Security=True");

                    connect.Open();

                    SqlCommand comm = new SqlCommand();

                    comm.Connection = connect;



                    string maVe = this.danhSachVedataGridView1.Rows[i].Cells[2].Value.ToString();

                    bool loaiNguoi = false;
                  
                    if (this.danhSachVedataGridView1.Rows[i].Cells[1].Value.ToString().ToLower() == "true" )
                    {

                        loaiNguoi = true;
                    }

                    comm.CommandText = "sp_banve_error";
                    comm.CommandType = CommandType.StoredProcedure;
                    comm.Parameters.Add(new SqlParameter("@MaVe", SqlDbType.VarChar, 10));
                    comm.Parameters["@MaVe"].Direction = ParameterDirection.Input;
                    comm.Parameters["@MaVe"].Value = maVe;

                    comm.Parameters.Add(new SqlParameter("@NguoiLon", SqlDbType.Bit));
                    comm.Parameters["@NguoiLon"].Direction = ParameterDirection.Input;
                    comm.Parameters["@NguoiLon"].Value = loaiNguoi;

                    comm.Parameters.Add(new SqlParameter("@TongTien", SqlDbType.Int));
                    comm.Parameters["@TongTien"].Direction = ParameterDirection.Output;
                    comm.Parameters["@TongTien"].Value = "";

                    comm.ExecuteNonQuery();
                    int result = int.Parse(comm.Parameters["@tongTien"].Value.ToString());
                    tongtien += result;

                    comm.Dispose();

                }
            }


            //LOAD LAI DU LIEU
            string mactsuatchieuSelected = "";
            try
            {

                if (this.ThoiGianChieucomboBox.SelectedValue != null)
                {
                    mactsuatchieuSelected = this.ThoiGianChieucomboBox.SelectedValue.ToString();
                    this.danhSachVedataGridView1.DataSource = SqlDataAccessHelper.ExecuteQuery("select v.nguoilon_treem, v.mave , v.tinhtrangve, dg.dayghe, dg.soghe, v.sdt, v.cmnd, v.hotenkh from ve v join dayghe_phongchieu dg on v.madaypc = dg.madaypc where v.tinhtrangve != 2 and v.MaCTSuatChieu = '" + mactsuatchieuSelected + "'");
                    for (int i = 2; i < int.Parse(this.danhSachVedataGridView1.Columns.Count.ToString()); i++)
                        this.danhSachVedataGridView1.Columns[i].ReadOnly = true;
                }
            }
            catch (Exception ex)
            {

            }

            MessageBox.Show("tổng tiền thanh toán: " + tongtien);

        }

        private void MuaFixButton_Click(object sender, EventArgs e)
        {
                       int tongtien = 0;

            int rows = int.Parse(this.danhSachVedataGridView1.RowCount.ToString());
            for (int i = 0; i < rows; i++)
            {

                if (this.danhSachVedataGridView1.Rows[i].Cells[0].Value != null)
                {

                    SqlConnection connect = new SqlConnection("Data Source=localhost ;Initial Catalog=QLRAPPHIM;Integrated Security=True");

                    connect.Open();

                    SqlCommand comm = new SqlCommand();

                    comm.Connection = connect;



                    string maVe = this.danhSachVedataGridView1.Rows[i].Cells[2].Value.ToString();

                    bool loaiNguoi = false;
                  
                    if (this.danhSachVedataGridView1.Rows[i].Cells[1].Value.ToString().ToLower() == "true" )
                    {

                        loaiNguoi = true;
                    }

                    comm.CommandText = "sp_banve_fix";
                    comm.CommandType = CommandType.StoredProcedure;
                    comm.Parameters.Add(new SqlParameter("@MaVe", SqlDbType.VarChar, 10));
                    comm.Parameters["@MaVe"].Direction = ParameterDirection.Input;
                    comm.Parameters["@MaVe"].Value = maVe;

                    comm.Parameters.Add(new SqlParameter("@NguoiLon", SqlDbType.Bit));
                    comm.Parameters["@NguoiLon"].Direction = ParameterDirection.Input;
                    comm.Parameters["@NguoiLon"].Value = loaiNguoi;

                    comm.Parameters.Add(new SqlParameter("@TongTien", SqlDbType.Int));
                    comm.Parameters["@TongTien"].Direction = ParameterDirection.Output;
                    comm.Parameters["@TongTien"].Value = "";

                    comm.ExecuteNonQuery();
                    int result = int.Parse(comm.Parameters["@tongTien"].Value.ToString());
                    tongtien += result;

                    comm.Dispose();

                }
            }


            //LOAD LAI DU LIEU
            string mactsuatchieuSelected = "";
            try
            {

                if (this.ThoiGianChieucomboBox.SelectedValue != null)
                {
                    mactsuatchieuSelected = this.ThoiGianChieucomboBox.SelectedValue.ToString();
                    this.danhSachVedataGridView1.DataSource = SqlDataAccessHelper.ExecuteQuery("select v.nguoilon_treem, v.mave , v.tinhtrangve, dg.dayghe, dg.soghe, v.sdt, v.cmnd, v.hotenkh from ve v join dayghe_phongchieu dg on v.madaypc = dg.madaypc where v.tinhtrangve != 2 and v.MaCTSuatChieu = '" + mactsuatchieuSelected + "'");
                    for (int i = 2; i < int.Parse(this.danhSachVedataGridView1.Columns.Count.ToString()); i++)
                        this.danhSachVedataGridView1.Columns[i].ReadOnly = true;
                }
            }
            catch (Exception ex)
            {

            }

            MessageBox.Show("tổng tiền thanh toán: " + tongtien);
        }
      
    }
}
